Using Optimized Multi-Attribute Hash Indexes for Hash Joins

The join operation is one of the most frequently used and expensive query processing operations in relational database systems. One method of joining two relations is to use a hash-based join algorithm. Hash-based join algorithms typically have two phases, a partitioning phase and a partition joining phase. We describe how an optimal multi-attribute hash (MAH) indexing scheme can be used to reduce the average cost of the partitioning phase of any hash-based join algorithm, by eliminating the partitioning phase entirely for many of the most common join queries. We demonstrate that the technique can be extended to include multiple copies of the data le, each with a diierent organization of the MAH indexing scheme, and that this further reduces the average cost of performing the partitioning phase of the hash join algorithm. We describe a relatively inexpensive method for determining a good MAH indexing scheme. Our experiments show that the schemes found using this method are usually optimal and performs the partitioning phase of the hash join algorithm at least three times faster than using the standard approach. We show that a signiicant change in the query pattern is required for a reorganization of the data le to be necessary, and show that reorganizing the data le is an inexpensive operation.